package cn.ddiancan.dm.service;

import cn.ddiancan.dm.dao.IDataMigrationLogDao;
import cn.ddiancan.dm.domain.DataMigrationDTO;
import cn.ddiancan.dm.dao.IDataMigrationDao;
import cn.ddiancan.dm.domain.DataMigrationLogDTO;
import cn.ddiancan.dm.mulidatasource.MultiDBS;
import cn.ddiancan.dm.mulidatasource.context.DataSourceContextHolder;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.commons.collections4.MapUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicInteger;

@Service
public class DataMigrationService {
    private static final Logger logger = LoggerFactory.getLogger(DataMigrationService.class);
    @Autowired
    private IDataMigrationDao dataMigrationDao;
    @Autowired
    private IDataMigrationLogDao dataMigrationLogDao;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Value("${ddc.data.backup.pagesize:1000}")
    private int pageSize;
    private static final Map<String,List<String>> TABLE_COMUMN_MAP = new ConcurrentHashMap<>();

    private List<String> getColumns(String tableName) {
        return dataMigrationDao.getColumns(tableName);
    }

    private String fetchAllColumn(String tableName) {
        List<String> columns = TABLE_COMUMN_MAP.get(tableName);
        if(CollectionUtils.isEmpty(columns)) {
            columns = this.getColumns(tableName);
            Collections.sort(columns);
            TABLE_COMUMN_MAP.put(tableName, columns);
        }
        StringBuilder sqlBuffer = new StringBuilder();
        columns.forEach(column -> sqlBuffer.append(",").append(column));
        return sqlBuffer.substring(1);
    }

    private String fetchSelectExecuteSql(String tableName, String type) {
        String columns = fetchAllColumn(tableName);
        StringBuilder sqlBuffer = new StringBuilder("SELECT ");
        if ("count".equals(type)) {
            sqlBuffer.append("COUNT(*)");
        } else {
            sqlBuffer.append(columns);
        }
        sqlBuffer.append(" FROM ").append(tableName);
        return sqlBuffer.toString();
    }

    public String fetchInsertExecuteSql(String tableName, List<Map<String, Object>> records) {
        String columns = fetchAllColumn(tableName);
        StringBuilder sqlBuffer = new StringBuilder("INSERT INTO ");
        sqlBuffer.append(tableName);
        sqlBuffer.append(" (");
        sqlBuffer.append(columns);
        sqlBuffer.append(") VALUES ");
        records.forEach(record -> {
            sqlBuffer.append("(");
            record.keySet().forEach(value -> sqlBuffer.append("?").append(","));
            sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
            sqlBuffer.append("),");
        });
        sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
        sqlBuffer.append("ON DUPLICATE KEY UPDATE ID= VALUES(ID)");
        return sqlBuffer.toString();
    }

    private String fetchDeleteExecuteSql(String tableName, List<Map<String, Object>> records) {
        StringBuilder sqlBuffer = new StringBuilder("DELETE FROM ");
        sqlBuffer.append(tableName);
        sqlBuffer.append(" WHERE ID IN (");
        records.forEach(record -> {
            sqlBuffer.append("?").append(",");
        });
        sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
        sqlBuffer.append(")");
        return sqlBuffer.toString();
    }

    private void fetchSelectExecuteSqlByCondition(DataMigrationDTO dataMigrationDTO, Map<String, String> sqlMap) {
        String executeSql = fetchSelectExecuteSql(dataMigrationDTO.getTableName(), dataMigrationDTO.getType());
        StringBuilder finalSql = new StringBuilder(executeSql);
        finalSql.append(" WHERE 1=1");
        if (StringUtils.hasText(dataMigrationDTO.getConditionDateColumnName())) {
            finalSql.append(" AND ").append(" DATE_FORMAT(").append(dataMigrationDTO.getConditionDateColumnName())
                    .append(",'%Y-%m-%d')").append(" BETWEEN ").append("?").append(" ").append("AND").append(" ").append("?");
        }
        if (!CollectionUtils.isEmpty(dataMigrationDTO.getSuperTableUniqueCodes())) {
            finalSql.append(" and ").append(dataMigrationDTO.getRefColumnName().get("ref")).append(" in (");
            dataMigrationDTO.getSuperTableUniqueCodes().forEach(code -> finalSql.append("?").append(","));
            finalSql.deleteCharAt(finalSql.length() - 1);
            finalSql.append(")").append(" order by id");
        }
        sqlMap.put(new StringBuilder(dataMigrationDTO.getTableName()).toString(), finalSql.toString());
        if (!"count".equals(dataMigrationDTO.getType()) && CollectionUtils.isEmpty(dataMigrationDTO.getSuperTableUniqueCodes())) {
            if (StringUtils.hasText(dataMigrationDTO.getConditionDateColumnName())) {
                String s = sqlMap.get(dataMigrationDTO.getTableName());
                sqlMap.put(new StringBuilder(dataMigrationDTO.getTableName()).toString(),
                        new StringBuilder(s).append(" limit ").append(pageSize).append(" offset ")
                                .append(dataMigrationDTO.getStartIndex()).toString());
            }
        }
    }

    public Integer searchByConditionCount(DataMigrationDTO dataMigrationDTO, Map<String, String> sqlMap) {
        fetchSelectExecuteSqlByCondition(dataMigrationDTO, sqlMap);
        DateTimeFormatter dateFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        LocalDate startTime = LocalDate.parse(dataMigrationDTO.getBeginDate(), dateFormat);
        LocalDate endTime = LocalDate.parse(dataMigrationDTO.getEndDate(), dateFormat);
        return jdbcTemplate.query(connection -> {
            PreparedStatement preparedStatement = connection.prepareStatement(sqlMap.get(dataMigrationDTO.getTableName()));
            preparedStatement.setDate(1, Date.valueOf(startTime));
            preparedStatement.setDate(2, Date.valueOf(endTime));
            return preparedStatement;
        }, resultSet -> resultSet.next() ? resultSet.getInt(1) : 0);
    }

    public List<Map<String, Object>> searchByCondition(DataMigrationDTO dataMigrationDTO, Map<String, String> sqlMap) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        fetchSelectExecuteSqlByCondition(dataMigrationDTO, sqlMap);
        String finalSql = sqlMap.get(dataMigrationDTO.getTableName());
        List<String> finalCodes = dataMigrationDTO.getSuperTableUniqueCodes();
        jdbcTemplate.query(connection -> {
                    PreparedStatement preparedStatement = connection.prepareStatement(finalSql.toString());
                    if (!CollectionUtils.isEmpty(finalCodes)) {
                        for (int i = 0; i < finalCodes.size(); i++) {
                            preparedStatement.setString(i + 1, finalCodes.get(i));
                        }
                    } else {
                        DateTimeFormatter dateFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                        LocalDate startTime = LocalDate.parse(dataMigrationDTO.getBeginDate(), dateFormat);
                        LocalDate endTime = LocalDate.parse(dataMigrationDTO.getEndDate(), dateFormat);
                        preparedStatement.setDate(1, Date.valueOf(startTime));
                        preparedStatement.setDate(2, Date.valueOf(endTime));
                    }
                    return preparedStatement;
                }, resultSet -> {
                    Map<String, Object> map = new TreeMap<>();
                    for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
                        map.put(resultSet.getMetaData().getColumnName(i + 1), resultSet.getObject(i + 1));
                    }
                    resultList.add(map);
                }
        );
        return resultList;
    }

    @MultiDBS("Slave")
    public Integer backHistoryDatasToSlave(List<Map<String, Object>> records, String executeSql) {
        return jdbcTemplate.execute((ConnectionCallback<Integer>) connection -> {
            connection.setAutoCommit(false);
            DataSourceContextHolder.setConnectionHolder(connection);
            PreparedStatement preparedStatement = connection.prepareStatement(executeSql);
            AtomicInteger index = new AtomicInteger(1);
            for (Map<String, Object> record : records) {
                for (Object value : record.values()) {
                    System.out.println(value);
                    if (value instanceof Number) {
                        preparedStatement.setInt(index.get(), new BigDecimal(value.toString()).intValue());
                        index.getAndIncrement();
                        continue;
                    }
                    if (value instanceof Date) {
                        preparedStatement.setDate(index.get(), (Date) value);
                        index.getAndIncrement();
                        continue;
                    }
                    if (value instanceof Boolean) {
                        preparedStatement.setBoolean(index.get(), Boolean.parseBoolean(value.toString()));
                        index.getAndIncrement();
                        continue;
                    }
                    preparedStatement.setString(index.get(), Objects.nonNull(value) ? value.toString() : null);
                    index.getAndIncrement();
                }
            }
            return preparedStatement.executeUpdate();
        });
    }

    @MultiDBS("Master")
    public Integer deleteHistoryDatas(String tableName, List<Map<String, Object>> records) {
        String deleteSql = fetchDeleteExecuteSql(tableName, records);
        return jdbcTemplate.execute((ConnectionCallback<Integer>) connection -> {
            connection.setAutoCommit(false);
            DataSourceContextHolder.setConnectionHolder(connection);
            PreparedStatement preparedStatement = connection.prepareStatement(deleteSql);
            int index = 1;
            for (Map<String, Object> record : records) {
                preparedStatement.setInt(index, MapUtils.getInteger(record, "id"));
                index++;
            }
            return preparedStatement.executeUpdate();
        });
    }

    @MultiDBS("Master")
    public int addDataMigrationLog(DataMigrationLogDTO dataMigrationLogDTO) {
        return dataMigrationLogDao.addDataMigrationLog(dataMigrationLogDTO);
    }

    @MultiDBS("Master")
    public int updateDataMigrationLog(List<DataMigrationLogDTO> logList) {
        return dataMigrationLogDao.updateDataMigrationLog(logList);
    }

    public PageInfo<DataMigrationLogDTO> logList(DataMigrationLogDTO dataMigrationLogDTO,Page page){
        PageHelper.startPage(page.getPageNum(), page.getPageSize());
        List<DataMigrationLogDTO> dataMigrationLogDTOList = dataMigrationLogDao.logList(dataMigrationLogDTO);
        return new PageInfo<>(dataMigrationLogDTOList);
    }
}
